﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Windows.Forms;

namespace CutterTool
{
    public class Worker
    {
        private List<cutterData> cudata;
        private string dbPath;
        private string tableName;

        public Worker()
        {
            cudata = new List<cutterData>();
            dbPath = Properties.Settings.Default.datasourcePath;
            tableName = Properties.Settings.Default.datasourceTable;
        }

        /// <summary>
        /// The main Worker method. This checks the datasource type and then calls
        /// the appropriate method to create an in-memory dataset
        /// </summary>
        public void createCutterList()
        {
            this.openTextFile();

            /*

            if (dbPath.EndsWith("accdb") || dbPath.EndsWith("mdb"))
            {
                this.openAccessDatabase();
            }
            else if (dbPath.EndsWith("xls") || dbPath.EndsWith("xlsx") || dbPath.EndsWith("xlsm"))
            {
                this.openExcelWorksheet();
            }
            else
            {
                this.openTextFile();
            }
            
            */
        }


        /// <summary>
        /// Loads a text file with cutter data. The data shoud be in the form
        /// of two columns, where the first is a string to compare against and the second is
        /// the corresponding code.
        /// </summary>
        private void openTextFile()
        {
            StreamReader sr = new StreamReader(dbPath);
            String line;
            while (!sr.EndOfStream)
            {
                line = sr.ReadLine();
                string[] spl = line.Split(',');
                cudata.Add(new cutterData(spl[0], spl[1]));
            }
            sr.Close();
            cudata.Sort();
        }

        /// <summary>
        /// Finds the index of the cutter code using simple iterative binary search
        /// </summary>
        /// <param name="name">Name to lookup</param>
        /// <param name="cuttercode">Return string for the Cutter code</param>
        /// <param name="prefix">Return string for Cutter code prefix</param>
        /// <param name="postfix">Return string for next Cutter code prefix</param>
        /// <returns></returns>
        public int findCutterCode(string name, out string cuttercode, out string prefix, out string postfix)
        {
            int min = 0;
            int max = cudata.Count;
            cutterData cuttercompare = new cutterData(name, "");
            int count = 0;
            while (max >= min)
            {
                int mid = (min + max) / 2;
                int comp = cuttercompare.CompareTo(cudata[mid]);
                if (comp == 0)
                {
                    max = mid;
                    min = mid + 1;
                    break;
                }
                else if (comp > 0)
                {
                    min = mid + 1;
                }
                else
                {
                    max = mid - 1;
                }
                count++;
            }

            // Edge cases, for Aa and Zz
            if (max >= cudata.Count - 1)
            {
                max = cudata.Count - 1;
                min = cudata.Count - 1;
            }
            if (max <= 0)
            {
                max = 0;
                min = 1;
            }

            // Set return values. Max will be less than Min after the while loop breaks
            cuttercode = cudata[max].cutter;
            prefix = cudata[max].text;
            postfix = cudata[min].text;

            return max;
        }

        #region Deprecated methods
        /*

        /// <summary>
        /// Loads an Access datatable and reads it into a List. The data shoud be in the form
        /// of two columns, where the first is a string to compare against and the second is
        /// the corresponding code.
        /// </summary>
        private void openAccessDatabase()
        {
            System.Data.OleDb.OleDbConnection conn = null;
            try
            {
                string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + dbPath;
                DataTable table = new DataTable();

                conn = new System.Data.OleDb.OleDbConnection(connectionString);
                conn.Open();
                string selectString = "SELECT * FROM " + tableName;
                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(selectString, conn);
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(cmd);
                adapter.Fill(table);

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    string text = table.Rows[i].ItemArray[0].ToString();
                    string cutter = table.Rows[i].ItemArray[1].ToString();
                    cutterData cu = new cutterData(text, cutter);
                    cudata.Add(cu);
                }

                cudata.Sort();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Access DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Loads an Excel spreadsheet and reads it into a List. The data shoud be in the form
        /// of two columns, where the first is a string to compare against and the second is
        /// the corresponding code. It will skip any lines where either the first or second
        /// element is missing.
        /// </summary>
        private void openExcelWorksheet()
        {
            System.Data.OleDb.OleDbConnection conn = null;
            try
            {

                string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + dbPath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                DataTable table = new DataTable();

                conn = new System.Data.OleDb.OleDbConnection(connectionString);
                conn.Open();

                string selectString = "SELECT * FROM [" + tableName + "]";
                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(selectString, conn);
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(cmd);
                adapter.Fill(table);

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    string text = table.Rows[i].ItemArray[0].ToString();
                    string cutter = table.Rows[i].ItemArray[1].ToString();
                    if (!text.Equals("") && !cutter.Equals(""))
                    {
                        cutterData cu = new cutterData(text, cutter);
                        cudata.Add(cu);
                    }
                }

                cudata.Sort();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Excel DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// These static members simply populate the combo box for the "Set Datasource" form.
        /// This method determines the type of data being used and calls the appropriate method.
        /// </summary>
        /// <param name="dbPath">Path to datasource</param>
        /// <returns>List of data tables or workbooks</returns>
        public static List<string> getTableList(string dbPath)
        {
            if (dbPath.EndsWith("accdb") || dbPath.EndsWith("mdb"))
            {
                return Worker.getTableListAccess(dbPath);
            }
            else
            {
                return Worker.getTableListExcel(dbPath);
            }
        }
        
        /// <summary>
        /// The Access version of the combo box populator.
        /// </summary>
        /// <param name="dbPath"></param>
        /// <returns></returns>
        private static List<string> getTableListAccess(string dbPath)
        {
            List<string> retList = new List<string>();
            try
            {
                string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + dbPath;
                DataTable schematable = new DataTable();

                using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString))
                {
                    conn.Open();
                    schematable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    for (int i = 0; i < schematable.Rows.Count; i++)
                    {
                        retList.Add(schematable.Rows[i]["TABLE_NAME"].ToString());
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Access DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //throw;
            }

            return retList;
        }

        /// <summary>
        /// The Excel version of the combo box populator.
        /// </summary>
        /// <param name="dbPath"></param>
        /// <returns></returns>
        private static List<string> getTableListExcel(string dbPath)
        {
            List<string> retList = new List<string>();
            try
            {
                string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + dbPath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                DataTable schematable = new DataTable();

                using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString))
                {
                    conn.Open();
                    schematable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    for (int i = 0; i < schematable.Rows.Count; i++)
                    {
                        retList.Add(schematable.Rows[i]["TABLE_NAME"].ToString());
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Excel DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //throw;
            }

            return retList;
        }

        */
        #endregion
    }
}
